package com.caiyi.financial.nirvana.sms.mapper;

import com.caiyi.financial.nirvana.sms.base.mapper.BaseMapper;
import com.caiyi.financial.nirvana.sms.entity.SmsUser;
import com.caiyi.financial.nirvana.sms.entity.SmsUserRecharge;
import com.caiyi.financial.nirvana.sms.querybean.QueryUserBean;
import com.caiyi.financial.nirvana.sms.querybean.QueryUserProductBean;
import com.caiyi.financial.nirvana.sms.querybean.UserRechargeQueryBean;
import com.caiyi.financial.nirvana.sms.vo.SmsUserProductListVo;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;

/**
 * Created by Mario on 2017/12/15 0015.
 * SMS客户Mapper
 */
@Mapper
public interface SmsUserMapper extends BaseMapper<SmsUser> {

    @Select("select account from sms_user")
    List<String> selectAllAccount();

    @Update("update sms_user set  sms_num=sms_num+#{value}  where id=#{id}")
    int addSmsNum(@Param("value") Integer value, @Param("id") Integer id);

    @Update("update sms_user set status=( case status when 1 then 2 else 1 end) where id=#{id}")
    int changeStatus(@Param("id") Integer id);

    @Update("update sms_user set password=#{newPassword} where id=#{id}")
    int updatePassword(@Param("newPassword") String newPassword, @Param("id") Integer id);

    @Select("select * from sms_user where account=#{account}")
    SmsUser selectByAccount(@Param("account") String account);

    @Select("select account from sms_user")
    List<String> selectAccountList();

    @Select("select password from sms_user where id=#{id}")
    String selectPassword(@Param("id") Integer id);

    @SelectProvider(type = UserSqlProvider.class, method = "selectUserList")
    List<SmsUser> selectUserList(QueryUserBean bean);

    @SelectProvider(type = UserSqlProvider.class, method = "selectProductListByParams")
    List<SmsUserProductListVo> selectProductListByParams(QueryUserProductBean bean);

    @Insert("insert into sms_user_recharge (account,type,sms_num,before_num,after_num,description,create_time,update_time,order_num)values(#{account},#{type},#{smsNum},#{beforeNum},#{afterNum},#{description},#{createTime},#{updateTime},#{orderNum})")
    int insertUserRecharge(SmsUserRecharge userRecharge);

    @SelectProvider(type = UserSqlProvider.class, method = "selectUserRechargeListByParams")
    List<SmsUserRecharge> selectUserRechargeListByParams(UserRechargeQueryBean bean);


    @SelectProvider(type = UserSqlProvider.class, method = "isExistAccount")
    int isExistAccount(SmsUser smsUser);

    @Select("select max(extend_code + 0) from sms_user")
    String getMaxExtendCode();

    class UserSqlProvider {

        public String isExistAccount(SmsUser smsUser) {
            return new SQL() {
                {
                    SELECT("count(id)");
                    FROM("sms_user");
                    WHERE("(account = #{account} || extend_code = #{extendCode})");
                    if (smsUser.getId() != null) {
                        WHERE("id != #{id}");
                    }
                }
            }.toString();
        }

        public String selectUserList(QueryUserBean bean) {
            return new SQL() {
                {
                    SELECT("id,account,type,charge_type,sms_num,extend_code,product_id,ip,name,phone,company_name,email,remind_value,remind_phone,status,audit_type,receive_url,day_count,hour_count,minute_count,create_time,update_time");
                    FROM("sms_user");
                    if (!StringUtils.isEmpty(bean.getAccount())) {
                        WHERE("account LIKE concat('%',#{account},'%')");
                    }
                    if (bean.getType() != null) {
                        WHERE("type=#{type}");
                    }
                    if (bean.getChargeType() != null) {
                        WHERE("charge_type=#{chargeType}");
                    }
                    if (bean.getStatus() != null) {
                        WHERE("status=#{status}");
                    }
                    if (bean.getStartTime() != null) {
                        WHERE("create_time >= #{startTime}");
                    }
                    if (bean.getEndTime() != null) {
                        WHERE("create_time <= date_add(#{endTime}, interval 1 day)");
                    }
                    ORDER_BY("id desc");

                }
            }.toString();
        }

        public String selectUserRechargeListByParams(UserRechargeQueryBean bean) {
            return new SQL() {
                {
                    SELECT("id,account,type,sms_num ,before_num,after_num,description,create_time,update_time,order_num");
                    FROM("sms_user_recharge");
                    if (!StringUtils.isEmpty(bean.getAccount())) {
                        WHERE("account LIKE concat('%',#{account},'%')");
                    }
                    if (!StringUtils.isEmpty(bean.getOrderNum())) {
                        WHERE("order_num=#{orderNum}");
                    }
                    if (bean.getType() != null) {
                        WHERE("type=#{type}");
                    }
                    if (bean.getStartTime() != null) {
                        WHERE("create_time >= #{startTime}");
                    }
                    if (bean.getEndTime() != null) {
                        WHERE("create_time <= date_add(#{endTime}, interval 1 day)");
                    }
                    ORDER_BY("id desc");
                }
            }.toString();
        }

        public String selectProductListByParams(QueryUserProductBean bean) {
            return new SQL() {
                {
                    SELECT("u.id,u.account,p.name,(SELECT GROUP_CONCAT(NAME ORDER BY FIND_IN_SET(id, yd_gateways)) FROM sms_gateway WHERE FIND_IN_SET(id, yd_gateways)) AS ydGatewayName," +
                            "(SELECT GROUP_CONCAT(NAME ORDER BY FIND_IN_SET(id, lt_gateways)) FROM sms_gateway WHERE FIND_IN_SET(id, lt_gateways)) AS ltGatewayName," +
                            "(SELECT GROUP_CONCAT(NAME ORDER BY FIND_IN_SET(id, dx_gateways)) FROM sms_gateway WHERE FIND_IN_SET(id, dx_gateways)) AS dxGatewayName," +
                            "u.create_time as createTime");
                    FROM("sms_user u");
                    LEFT_OUTER_JOIN("sms_product p on u.product_id=p.id");
                    if (!StringUtils.isEmpty(bean.getAccount())) {
                        WHERE("u.account LIKE concat('%',#{account},'%')");
                    }
                    if (!StringUtils.isEmpty(bean.getName())) {
                        WHERE("p.name LIKE concat('%',#{name},'%')");
                    }
                    if (bean.getStartTime() != null) {
                        WHERE("u.create_time >= #{startTime}");
                    }
                    if (bean.getEndTime() != null) {
                        WHERE("u.create_time <= date_add(#{endTime}, interval 1 day)");
                    }
                    ORDER_BY("id desc");
                }
            }.toString();
        }
    }
}
